package demo1.dao.sql;

import demo1.dao.Mapper.goodsMapper;
import demo1.pojo.*;
import demo1.utils.connention;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;

public class goodsSql implements goodsMapper {
    @Override
    public List<goods> selectById(int id) throws Exception {

        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "select * from goods where id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, id);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<goods> goodsList = new ArrayList<>();
        goods goods;
        while (rs.next()) {
            int price = rs.getInt("price");//价格
            int goodsNum = rs.getInt("goods_num");//库存
            int goodsXiaoLiang = rs.getInt("goods_xiaoliang");//销量
            int goodsID = rs.getInt("goods_id");
            int goodsGreat = rs.getInt("goods_great");//好评数
            String goodsMessage = rs.getString("goods_message");//商品介绍
            String goodsName = rs.getString("goods_name");//商品名称
            String goodsType = rs.getString("goods_type");//商品种类
            String statue = rs.getString("statue");//商品状态
            Timestamp time = rs.getTimestamp("time");//商品上架时间
            String formattedDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(time);

            goods = new goods();
            goods.setGoodsMessage(goodsMessage);
            goods.setGoodsNum(goodsNum);
            goods.setGoodsName(goodsName);
            goods.setGoodsType(goodsType);
            goods.setGoodsXiaoLiang(goodsXiaoLiang);
            goods.setId(rs.getInt("id"));
            goods.setPrice(price);
            goods.setStatue(statue);
            goods.setDate(formattedDate);
            goods.setGoodsID(goodsID);
            goods.setId(id);
            goods.setGoodsGreat(goodsGreat);
            // 添加到列表中
            goodsList.add(goods);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return goodsList;
    }

    @Override
    public List<goods_type> selectAllGoodsType() throws Exception {

        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "select * from goods_type";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<goods_type> goods_typeList = new ArrayList<>();
        goods_type goods_type;
        while (rs.next()) {
            int goodsID = rs.getInt("goods_id");
            String goodsType = rs.getString("type");//商品种类

            goods_type = new goods_type();
            goods_type.setType(goodsType);
            goods_type.setGoodsID(goodsID);
            // 添加到列表中
            goods_typeList.add(goods_type);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return goods_typeList;
    }

    @Override
    public List<cancelOrder> selectCancelOrderById(int userID) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "select * from cancel_order where usermai_id = ? and statue = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, userID);
        pstmt.setInt(2, 0);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<cancelOrder> cancelOrderList = new ArrayList<>();
        cancelOrder cancelOrder;
        while (rs.next()) {
            int price = rs.getInt("price");//价格
            int goodsNum = rs.getInt("goods_num");//库存
            int orderID = rs.getInt("order_id");
            int goodsID = rs.getInt("goods_id");
            int userMaiID = rs.getInt("usermai_id");
            int userBuyID = rs.getInt("userbuy_id");
            int orderFormID = rs.getInt("orderform_id");
            int statue = rs.getInt("statue");//好评数
            int totalPrice = rs.getInt("total_price");//好评数
            String userName = rs.getString("user_name");//商品介绍
            String goodsName = rs.getString("goods_name");//商品名称
            String userPhone = rs.getString("user_phone");//商品种类
            String userAddress = rs.getString("user_address");//商品状态
            String cancelReason = rs.getString("cancel_reason");//商品状态

            //订单未退款
            cancelOrder = new cancelOrder();
            cancelOrder.setPrice(price);
            cancelOrder.setGoodsNum(goodsNum);
            cancelOrder.setOrderID(orderID);
            cancelOrder.setGoodsID(goodsID);
            cancelOrder.setUserMaiID(userMaiID);
            cancelOrder.setUserBuyID(userBuyID);
            cancelOrder.setOrderFormID(orderFormID);
            cancelOrder.setOrderStatue("待退款");
            cancelOrder.setStatue(statue);
            cancelOrder.setTotalPrice(totalPrice);
            cancelOrder.setUserName(userName);
            cancelOrder.setGoodsName(goodsName);
            cancelOrder.setUserPhone(userPhone);
            cancelOrder.setUserAddress(userAddress);
            cancelOrder.setCancelReason(cancelReason);
            // 添加到列表中
            cancelOrderList.add(cancelOrder);

        }
        rs.close();
        pstmt.close();
        conn.close();
        return cancelOrderList;
    }

    @Override
    public List<goodsComment> lookGoodsComment(int goodsID) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "select * from goods_comment where goods_id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, goodsID);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<goodsComment> goodsCommentList = new ArrayList<>();
        goodsComment goodsComment;
        while (rs.next()) {
            int userID = rs.getInt("user_id");//价格
            String goodsComments = rs.getString("goods_comment");//商品介绍
            Timestamp time = rs.getTimestamp("comment_time");//商品上架时间
            String formattedDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(time);

            //订单未退款
            goodsComment = new goodsComment();
            goodsComment.setGoodsComment(goodsComments);
            goodsComment.setDate(formattedDate);
            goodsComment.setGoodsID(goodsID);
            goodsComment.setUserID(userID);
            goodsComment.setCommentTime(time);
            // 添加到列表中
            goodsCommentList.add(goodsComment);

        }
        rs.close();
        pstmt.close();
        conn.close();
        return goodsCommentList;
    }

    @Override
    public List<goods> selectByPage(int begin, int size) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "select * from goods where statue = ? limit ?,?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, "上架");
        pstmt.setInt(2, begin);
        pstmt.setInt(3, size);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<goods> goodsList = new ArrayList<>();
        goods goods;
        while (rs.next()) {
            int price = rs.getInt("price");//价格
            int goodsNum = rs.getInt("goods_num");//库存
            int goodsXiaoLiang = rs.getInt("goods_xiaoliang");//销量
            int goodsID = rs.getInt("goods_id");
            int goodsGreat = rs.getInt("goods_great");//好评数
            int id = rs.getInt("id");
            int lookNum = rs.getInt("look_num");//浏览量
            String goodsMessage = rs.getString("goods_message");//商品介绍
            String goodsName = rs.getString("goods_name");//商品名称
            String goodsType = rs.getString("goods_type");//商品种类
            String statue = rs.getString("statue");//商品状态
            String shopName = rs.getString("shop_name");//小店名称
            String url = rs.getString("url");//图片路径
            Timestamp time = rs.getTimestamp("time");//商品上架时间
            String formattedDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(time);

            goods = new goods();
            goods.setGoodsMessage(goodsMessage);
            goods.setGoodsNum(goodsNum);
            goods.setGoodsName(goodsName);
            goods.setGoodsType(goodsType);
            goods.setGoodsXiaoLiang(goodsXiaoLiang);
            goods.setId(rs.getInt("id"));
            goods.setPrice(price);
            goods.setStatue(statue);
            goods.setDate(formattedDate);
            goods.setGoodsID(goodsID);
            goods.setId(id);
            goods.setShopName(shopName);
            goods.setGoodsGreat(goodsGreat);
            goods.setLookNum(lookNum);
            goods.setUrl(url);
            // 添加到列表中
            goodsList.add(goods);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return goodsList;
    }

    @Override
    public List<goods> onSubmit(int begin, int size, goods goods2) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "select * from goods where statue = ? and goods_name like ? and goods_type like ? and shop_name like ? limit ?,?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, "上架");
        pstmt.setString(2, "%"+goods2.getGoodsName()+"%");
        pstmt.setString(3, "%"+goods2.getGoodsType()+"%");
        pstmt.setString(4, "%"+goods2.getShopName()+"%");
        pstmt.setInt(5, begin);
        pstmt.setInt(6, size);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<goods> goodsList = new ArrayList<>();
        goods goods;
        while (rs.next()) {
            int price = rs.getInt("price");//价格
            int goodsNum = rs.getInt("goods_num");//库存
            int goodsXiaoLiang = rs.getInt("goods_xiaoliang");//销量
            int goodsID = rs.getInt("goods_id");
            int goodsGreat = rs.getInt("goods_great");//好评数
            int id = rs.getInt("id");//好评数
            int lookNum = rs.getInt("look_num");//浏览量
            String goodsMessage = rs.getString("goods_message");//商品介绍
            String goodsName = rs.getString("goods_name");//商品名称
            String goodsType = rs.getString("goods_type");//商品种类
            String statue = rs.getString("statue");//商品状态
            String shopName = rs.getString("shop_name");//小店名称
            String url = rs.getString("url");//图片路径
            Timestamp time = rs.getTimestamp("time");//商品上架时间
            String formattedDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(time);

            goods = new goods();
            goods.setGoodsMessage(goodsMessage);
            goods.setGoodsNum(goodsNum);
            goods.setGoodsName(goodsName);
            goods.setGoodsType(goodsType);
            goods.setGoodsXiaoLiang(goodsXiaoLiang);
            goods.setId(rs.getInt("id"));
            goods.setPrice(price);
            goods.setStatue(statue);
            goods.setDate(formattedDate);
            goods.setGoodsID(goodsID);
            goods.setId(id);
            goods.setShopName(shopName);
            goods.setGoodsGreat(goodsGreat);
            goods.setLookNum(lookNum);
            goods.setUrl(url);
            // 添加到列表中
            goodsList.add(goods);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return goodsList;
    }

    @Override
    public List<goods> selectByPageMai(int begin, int size, int userID) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "SELECT * FROM goods WHERE id = ? LIMIT ?, ?;";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, userID);
        pstmt.setInt(2, begin);
        pstmt.setInt(3, size);
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<goods> goodsList = new ArrayList<>();
        goods goods;
        while (rs.next()) {
            int price = rs.getInt("price");//价格
            int goodsNum = rs.getInt("goods_num");//库存
            int goodsXiaoLiang = rs.getInt("goods_xiaoliang");//销量
            int goodsID = rs.getInt("goods_id");
            int goodsGreat = rs.getInt("goods_great");//好评数
            int id = rs.getInt("id");//好评数
            int lookNum = rs.getInt("look_num");//浏览量
            String goodsMessage = rs.getString("goods_message");//商品介绍
            String goodsName = rs.getString("goods_name");//商品名称
            String goodsType = rs.getString("goods_type");//商品种类
            String statue = rs.getString("statue");//商品状态
            String shopName = rs.getString("shop_name");//小店名称
            String url = rs.getString("url");//小店名称
            Timestamp time = rs.getTimestamp("time");//商品上架时间
            String formattedDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(time);

            goods = new goods();
            goods.setGoodsMessage(goodsMessage);
            goods.setGoodsNum(goodsNum);
            goods.setGoodsName(goodsName);
            goods.setGoodsType(goodsType);
            goods.setGoodsXiaoLiang(goodsXiaoLiang);
            goods.setId(rs.getInt("id"));
            goods.setPrice(price);
            goods.setStatue(statue);
            goods.setDate(formattedDate);
            goods.setGoodsID(goodsID);
            goods.setId(id);
            goods.setShopName(shopName);
            goods.setGoodsGreat(goodsGreat);
            goods.setLookNum(lookNum);
            goods.setUrl(url);
            // 添加到列表中
            goodsList.add(goods);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return goodsList;
    }

    @Override
    public int selectTotalCounts() throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "select * from goods where statue = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //执行sql
        pstmt.setString(1, "上架");
        ResultSet rs = pstmt.executeQuery();
        int num = 0;
        while (rs.next()){
            num++;
        }
        rs.close();
        pstmt.close();
        conn.close();
        return num;
    }

    @Override
    public int selectTotalCountsMai(int userID) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "select * from goods where id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //执行sql
        pstmt.setInt(1, userID);
        ResultSet rs = pstmt.executeQuery();
        int num = 0;
        while (rs.next()){
            num++;
        }
        rs.close();
        pstmt.close();
        conn.close();
        return num;
    }

    @Override
    public int selectTotalCountsSubmit(goods goods2) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "select * from goods where statue = ? and goods_name = ? and goods_type = ? and shop_name = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //执行sql
        pstmt.setString(1, "上架");
        pstmt.setString(2, "%"+goods2.getGoodsName()+"%");
        pstmt.setString(3, "%"+goods2.getGoodsType()+"%");
        pstmt.setString(4, "%"+goods2.getShopName()+"%");
        ResultSet rs = pstmt.executeQuery();
        int num = 0;
        while (rs.next()){
            num++;
        }
        rs.close();
        pstmt.close();
        conn.close();
        return num;
    }

    @Override
    public void addGoods(float price,String goodsMessage,String goodsName,String goodsType,int goodsNum,int id,String shopName) throws Exception {

        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "insert into goods(price,goods_message,goods_name,goods_num,goods_xiaoliang,goods_type,statue,id,goods_great,shop_name) values (?,?,?,?,?,?,?,?,?,?)";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setFloat(1, price);
        pstmt.setString(2, goodsMessage);
        pstmt.setString(3, goodsName);
        pstmt.setInt(4, goodsNum);
        pstmt.setInt(5, 0);
        pstmt.setString(6, goodsType);
        pstmt.setString(7, "下架");
        pstmt.setInt(8, id);
        pstmt.setInt(9, 0);
        pstmt.setString(10, shopName);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void addGoodsType(String type, int goodsID) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "insert into goods_type(type,goods_id) values (?,?)";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setString(1, type);
        pstmt.setInt(2, goodsID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void insertGoodsComment(String goodsComment,int goodsID, int userID) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "insert into goods_comment(goods_comment,goods_id,user_id) values (?,?,?)";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setString(1, goodsComment);
        pstmt.setInt(2, goodsID);
        pstmt.setInt(3, userID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void insertCancelOrder(String cancelReason, String goodsName,int goodsID, int userBuyID, int userMaiID, int price, int goodsNum,String userName,String userPhone,String userAddress,int orderFormID) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "insert into cancel_order(usermai_id,goods_id,goods_name,statue,price,goods_num,total_price,cancel_reason,userbuy_id,user_name,user_phone,user_address,orderform_id) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        int totalPrice = price*goodsNum;
        //设置？的值
        pstmt.setInt(1, userMaiID);
        pstmt.setInt(2, goodsID);
        pstmt.setString(3, goodsName);
        pstmt.setInt(4, 0);
        pstmt.setInt(5, price);
        pstmt.setInt(6, goodsNum);
        pstmt.setInt(7, totalPrice);
        pstmt.setString(8, cancelReason);
        pstmt.setInt(9, userBuyID);
        pstmt.setString(10, userName);
        pstmt.setString(11, userPhone);
        pstmt.setString(12, userAddress);
        pstmt.setInt(13, orderFormID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void modifyGoodsAndTime(float price, String goodsMessage, String goodsName, String goodsType, int goodsNum, int goodsID, String statue) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "update goods set price = ?,goods_message = ?,goods_name = ?,goods_num = ?,goods_type = ?,statue = ?,time = ? where goods_id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //获取当前时间
        Date currentDate = new Date();

        // 创建Timestamp实例
        Timestamp timestamp = new Timestamp(currentDate.getTime());
        //设置？的值
        pstmt.setFloat(1, price);
        pstmt.setString(2, goodsMessage);
        pstmt.setString(3, goodsName);
        pstmt.setInt(4, goodsNum);
        pstmt.setString(5, goodsType);
        pstmt.setString(6, statue);
        pstmt.setTimestamp(7, timestamp);
        pstmt.setInt(8, goodsID);

        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void modifyGoods(float price, String goodsMessage, String goodsName, String goodsType, int goodsNum, int goodsID,String statue) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "update goods set price = ?,goods_message = ?,goods_name = ?,goods_num = ?,goods_type = ?,statue = ? where goods_id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setFloat(1, price);
        pstmt.setString(2, goodsMessage);
        pstmt.setString(3, goodsName);
        pstmt.setInt(4, goodsNum);
        pstmt.setString(5, goodsType);
        pstmt.setString(6, statue);
        pstmt.setInt(7, goodsID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void modifyGoodsNum(int goodsNum, int goodsID) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "update goods set goods_num = ? where goods_id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, goodsNum);
        pstmt.setInt(2, goodsID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void modifyGoodsStatue(String statue, int goodsID) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "update goods set statue = ? where goods_id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setString(1, statue);
        pstmt.setInt(2, goodsID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void modifyGoodsUrl(String url, int goodsID) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "update goods set url = ? where goods_id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setString(1, url);
        pstmt.setInt(2, goodsID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void modifyGoodsShopName(String shopName, int userID) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "update goods set shop_name = ? where id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setString(1, shopName);
        pstmt.setInt(2, userID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void modifyGoodsXiaoLiang(int goodsXiaoLiang, int goodsID) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "update goods set goods_xiaoliang = ? where goods_id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, goodsXiaoLiang);
        pstmt.setInt(2, goodsID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void modifyGoodsGreat(int goodsGreat, int goodsID) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "update goods set goods_great = ? where goods_id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, goodsGreat);
        pstmt.setInt(2, goodsID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void modifyGoodsLookNum(int lookNum, int goodsID) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "update goods set look_num = ? where goods_id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, lookNum);
        pstmt.setInt(2, goodsID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }

    @Override
    public void modifyCancelOrderStatue(int statue, int orderID) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "update cancel_order set statue = ? where order_id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置？的值
        pstmt.setInt(1, statue);
        pstmt.setInt(2, orderID);
        //执行sql
        pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    }





    @Override
    public ResultSet selectByID(int goodsID) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "select * from goods where goods_id = ?";
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, goodsID);
        //执行sql
        return pstmt.executeQuery();
    }

    @Override
    public void deleteGoods(int[] ids) throws Exception {
        for (int i = 0; i < ids.length; i++) {
            connention connention = new connention();
            Connection conn = connention.conn();
            //Connection conn = conn();
            String sql = "delete from goods where goods_id = ?";
            //获取pstmt对象
            PreparedStatement pstmt = conn.prepareStatement(sql);

            //设置？的值
            pstmt.setInt(1, ids[i]);
            //执行sql
            pstmt.executeUpdate();
            pstmt.close();
            conn.close();
        }

    }
    @Override
    public void deleteGoods2(int ids) throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
            String sql = "delete from goods where goods_id = ?";
            //获取pstmt对象
            PreparedStatement pstmt = conn.prepareStatement(sql);

            //设置？的值
            pstmt.setInt(1, ids);
            //执行sql
            pstmt.executeUpdate();
            pstmt.close();
            conn.close();
    }



    @Override
    public List<goods> selectAllEarly() throws Exception {
        connention connention = new connention();
        Connection conn = connention.conn();
        //Connection conn = conn();
        String sql = "select * from goods where statue = ? ORDER BY time ASC";//时间从早到晚,ORDER BY time desc从晚到早
        //获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, "上架");
        //执行sql
        ResultSet rs = pstmt.executeQuery();
        List<goods> goodsList = new ArrayList<>();
        goods goods;
        while (rs.next()) {
            int price = rs.getInt("price");//价格
            int goodsNum = rs.getInt("goods_num");//库存
            int goodsXiaoLiang = rs.getInt("goods_xiaoliang");//销量
            int goodsID = rs.getInt("goods_id");
            int id = rs.getInt("id");//商家id
            int goodsGreat = rs.getInt("goods_great");//好评数
            int lookNum = rs.getInt("look_num");
            String goodsMessage = rs.getString("goods_message");//商品介绍
            String goodsName = rs.getString("goods_name");//商品名称
            String goodsType = rs.getString("goods_type");//商品种类
            String statue = rs.getString("statue");//商品状态
            String url = rs.getString("url");//图片路径
            Timestamp time = rs.getTimestamp("time");//商品上架时间
            String formattedDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(time);

            goods = new goods();
            goods.setGoodsMessage(goodsMessage);
            goods.setGoodsNum(goodsNum);
            goods.setGoodsName(goodsName);
            goods.setGoodsType(goodsType);
            goods.setGoodsXiaoLiang(goodsXiaoLiang);
            goods.setId(rs.getInt("id"));
            goods.setPrice(price);
            goods.setStatue(statue);
            goods.setDate(formattedDate);
            goods.setGoodsID(goodsID);
            goods.setId(id);
            goods.setGoodsGreat(goodsGreat);
            goods.setLookNum(lookNum);
            goods.setUrl(url);
            // 添加到列表中
            goodsList.add(goods);
        }
        rs.close();
        pstmt.close();
        conn.close();
        return goodsList;
    }
//    public Connection conn()throws Exception{
//        Class.forName("com.mysql.cj.jdbc.Driver");
//        //2.获取连接
//        String url = "jdbc:mysql://127.0.0.1:3306/project2";
//        String user = "root";
//        String password = "111214Cjj";
//
//        Connection conn = DriverManager.getConnection(url, user, password);
//        return conn;
//    }
}
